﻿CREATE PROCEDURE [dbo].[proc_Finance_Subject_Report]
	(
		@ParType int,
		@ParDay nchar(10),
		@ParMonth nchar(8),
		@ParQuarter int,
		@ParYear nchar(4),
		@AccountsTypeId int
	)
AS
begin
	if(@ParType=1)
		begin
			Select
			S.Title,
			(Select Count(Id) From Finance_RunningAccount Where SubjectId=S.Id and Convert(nchar(10),CreateDate,120)=@ParDay) As Number,
			IsNull((Select Sum(Case When @AccountsTypeId=1 Then Income Else Outlay End) From Finance_RunningAccount Where SubjectId=S.Id and Convert(nchar(10),CreateDate,120)=@ParDay),0) As [Money]
			From Finance_Subject S Where TypeId=@AccountsTypeId Order by [Money] DESC
		end
	else if(@ParType=2)
		begin
			Select
			S.Title,
			(Select Count(Id) From Finance_RunningAccount Where SubjectId=S.Id and Convert(nchar(7),CreateDate,120)=@ParMonth) As Number,
			IsNull((Select Sum(Case When @AccountsTypeId=1 Then Income Else Outlay End) From Finance_RunningAccount Where SubjectId=S.Id and Convert(nchar(7),CreateDate,120)=@ParMonth),0) As [Money]
			From Finance_Subject S Where TypeId=@AccountsTypeId Order by [Money] DESC
		end
	else if(@ParType=3)
		begin
			Declare @StartMonth int,@IMonth int,@EndMonth int
			Set @StartMonth=((@ParQuarter-1)*3)+1
			Set @IMonth=@StartMonth+1
			Set @EndMonth=@StartMonth+2
			Select
			S.Title,
			IsNull((Select Sum(Case When @AccountsTypeId=1 Then Income Else Outlay End) From Finance_RunningAccount Where SubjectId=S.Id and Year(CreateDate)=@ParYear And Month(CreateDate)=@StartMonth),0) As StartMonth,
			IsNull((Select Sum(Case When @AccountsTypeId=1 Then Income Else Outlay End) From Finance_RunningAccount Where SubjectId=S.Id and Year(CreateDate)=@ParYear And Month(CreateDate)=@IMonth),0) As IMonth,
			IsNull((Select Sum(Case When @AccountsTypeId=1 Then Income Else Outlay End) From Finance_RunningAccount Where SubjectId=S.Id and Year(CreateDate)=@ParYear And Month(CreateDate)=@EndMonth),0) As EndMonth
			From Finance_Subject S Where TypeId=@AccountsTypeId Order by S.Sort
		end
	else
		begin
			Select
			S.Title,
			IsNull((Select Sum(Case When @AccountsTypeId=1 Then Income Else Outlay End) From Finance_RunningAccount Where SubjectId=S.Id and Year(CreateDate)=@ParYear And Month(CreateDate) in (1,2,3)),0) As Q1,
			IsNull((Select Sum(Case When @AccountsTypeId=1 Then Income Else Outlay End) From Finance_RunningAccount Where SubjectId=S.Id and Year(CreateDate)=@ParYear And Month(CreateDate) in (4,5,6)),0) As Q2,
			IsNull((Select Sum(Case When @AccountsTypeId=1 Then Income Else Outlay End) From Finance_RunningAccount Where SubjectId=S.Id and Year(CreateDate)=@ParYear And Month(CreateDate) in (7,8,9)),0) As Q3,
			IsNull((Select Sum(Case When @AccountsTypeId=1 Then Income Else Outlay End) From Finance_RunningAccount Where SubjectId=S.Id and Year(CreateDate)=@ParYear And Month(CreateDate) in (10,11,12)),0) As Q4
			From Finance_Subject S Where TypeId=@AccountsTypeId Order by S.Sort
		end
end
	RETURN
